package cn.ffcs.uec.view.action;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.junit.Test;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;
import org.zkoss.util.media.AMedia;
import org.zkoss.web.servlet.Servlets;
import org.zkoss.zk.ui.Component;
import org.zkoss.zk.ui.Components;
import org.zkoss.zk.ui.Executions;
import org.zkoss.zk.ui.event.Events;
import org.zkoss.zul.Filedownload;

import cn.ffcs.raptornuke.plugin.common.zk.ctrl.BasePortletComposer;
import cn.ffcs.raptornuke.portal.kernel.xml.DocumentException;
import cn.ffcs.raptornuke.portal.theme.ThemeDisplay;
import cn.ffcs.uec.staff.manager.UecAssManager;
import cn.ffcs.uec.staff.manager.UecDocEduManager;
import cn.ffcs.uec.staff.manager.UecEncManager;
import cn.ffcs.uec.staff.manager.UecFamilyManager;
import cn.ffcs.uec.staff.manager.UecNationdutyManager;
import cn.ffcs.uec.staff.manager.UecTitleManager;
import cn.ffcs.uec.staff.manager.UecWorkManager;
import cn.ffcs.uec.staff.model.UecAss;
import cn.ffcs.uec.staff.model.UecDocEdu;
import cn.ffcs.uec.staff.model.UecEnc;
import cn.ffcs.uec.staff.model.UecFamily;
import cn.ffcs.uec.staff.model.UecNationduty;
import cn.ffcs.uec.staff.model.UecTitle;
import cn.ffcs.uec.staff.model.UecWork;
import cn.ffcs.uec.staffAndOutStaff.vo.StaffAndOutStaff;
import cn.ffcs.uec.view.action.bean.UecStaffInfomationMainBean;
import cn.ffcs.uom.common.util.ApplicationContextUtil;
import cn.ffcs.uom.common.util.IPortletInfoProvider;
import cn.ffcs.uom.common.util.StrUtil;
import cn.ffcs.uom.common.util.UomZkUtil;

/**
 * @desc 人员档案表格设计
 * @author BMP
 * @time 2019-04-12
 */
@Controller
@Scope("prototype")
public class UecStaffInfomationMainComposer extends BasePortletComposer
		implements IPortletInfoProvider {
	private static final long serialVersionUID = -139335897781983790L;
	private UecStaffInfomationMainBean bean = new UecStaffInfomationMainBean();
	private StaffAndOutStaff staffAndOutStaff = new StaffAndOutStaff();
	private UecAssManager uecAssManager = (UecAssManager) ApplicationContextUtil
			.getBean("uecAssManager");
	private UecWorkManager uecWorkManager = (UecWorkManager) ApplicationContextUtil
			.getBean("uecWorkManager");
	private UecEncManager uecEncManager = (UecEncManager) ApplicationContextUtil
			.getBean("uecEncManager");
	private UecDocEduManager uecEduManager = (UecDocEduManager) ApplicationContextUtil
			.getBean("uecDocEduManager");
	//职业资格
	private UecNationdutyManager uecNationdutyManager = (UecNationdutyManager) ApplicationContextUtil
			.getBean("uecNationdutyManager");
	//专业技术职务
	private UecTitleManager uecTitleManager = (UecTitleManager) ApplicationContextUtil
			.getBean("uecTitleManager");
	//家庭信息
	private UecFamilyManager uecFamilyManager = (UecFamilyManager) ApplicationContextUtil
			.getBean("uecFamilyManager");
	List<UecAss> findUecAssByPkPsndoc=null;
	List<UecWork> findUecWorkByPkPsndoc=null;
	List<UecEnc> findUecEncByPkPsndoc=null;
	List<UecDocEdu> findUecDocEduByPkPsndoc=null;
	List<UecNationduty> uecNationdutyListByPkpsndoc = null;
	List<UecTitle> uecTitleListByPkpsndoc=null;
	List<UecFamily> uecFamilyListByPkpsndoc=null;
	//定义列表行，为了公用
	HSSFRow row=null;
	//定义列表行单元格，为了公用
	HSSFCell beginCell=null;
	@Override
	public String getPortletId() {
		return super.getPortletId();
	}

	@Override
	public ThemeDisplay getThemeDisplay() {
		return super.getThemeDisplay();
	}
	@Override
	public void doAfterCompose(Component comp) throws Exception {
		super.doAfterCompose(comp);
		UomZkUtil.autoFitHeight(comp);
		Components.wireVariables(comp, bean);
	}

	/**
	 * 初始化窗口
	 * @throws Exception 
	 */
	public void onCreate$uecStaffInfomationMainWin() throws Exception {
		staffAndOutStaff = (StaffAndOutStaff)arg.get("staffAndOutStaff");
		this.binbBean();
		//this.bindCombobox();
			//byte[] bsPdfView = exportExcel();
			//org.zkoss.util.media.Media media = new AMedia(null, null, "application/vnd.ms-excel", bsPdfView);
           // bean.getIframeexcel().setHeight("600px");
           // bean.getIframeexcel().setContent(media);
	}

	/**
	 * @throws Exception 
	 * 获取全局变量，绑定数据导页面
	 * @Title: binbBean   
	 * @Description: TODO 
	 * @param: @throws InterruptedException      
	 * @return: void      
	 * @throws
	 */
	private void binbBean() throws Exception{
		if(staffAndOutStaff != null ) {
			findUecAssByPkPsndoc = uecAssManager.findUecAssByPkPsndoc(staffAndOutStaff);
			findUecWorkByPkPsndoc=uecWorkManager.findUecWorkByPkPsndoc(staffAndOutStaff);
			findUecEncByPkPsndoc=uecEncManager.findUecEncByPkPsndoc(staffAndOutStaff);
			findUecDocEduByPkPsndoc=uecEduManager.findUecDocEduByPkPsndoc(staffAndOutStaff);
			uecNationdutyListByPkpsndoc = uecNationdutyManager.getUecNationdutyListByPkpsndoc(staffAndOutStaff);
			uecTitleListByPkpsndoc = uecTitleManager.getUecTitleListByPkpsndoc(staffAndOutStaff);
			uecFamilyListByPkpsndoc=uecFamilyManager.getUecFamilyListByPkpsndoc(staffAndOutStaff);
		}
	}

	/**
	 * @throws Exception 
	 * @throws ParseException 
	 * @throws InterruptedException
	 *             员工报名表导出按钮，通过按钮触发
	 * @Title: onOk
	 * @Description: TODO
	 * @param:
	 * @return: void
	 * @throws
	 */
	public void onOk() throws Exception {
		try {
			String charset = "UTF-8";
            // 图片名称
            String fileName = staffAndOutStaff.getStaffName()+"档案";
            // 编码后文件名
            String encodedName = null;
            encodedName = URLEncoder.encode(fileName, charset);
            byte[] bsPdf = exportExcel();
            // 将空格替换为+号
            encodedName = encodedName.replace("%20", "+");
            HttpServletRequest httpRequest = (HttpServletRequest) Executions.getCurrent()
                    .getNativeRequest();
            // 解决ie6 bug 或者是火狐浏览器
            if (encodedName.length() > 150 || Servlets.isGecko(httpRequest)
                    || Servlets.isGecko3(httpRequest)) {
                encodedName = new String(fileName.getBytes(charset), "ISO8859-1");
            }
			Filedownload.save(bsPdf, "application/vnd.ms-excel", fileName);
			Events.postEvent("onOK", this.self, null);
		} catch (DocumentException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (com.itextpdf.text.DocumentException e) {
			e.printStackTrace();
		}
	}
	/**
	 * @throws Exception 
	 * @Title: onCencel
	 * @Description: TODO
	 * @throws
	 */
	public void onCencel() throws Exception {
		this.bean.getUecStaffInfomationMainWin().onClose();
	}

	/**
	 * @desc 生成EXCEL
	 * @return: byte[] 
	 * @throws DocumentException
	 * @throws IOException
	 * @throws com.itextpdf.text.DocumentException
	 * @throws ParseException 
	 * @throws InterruptedException
	 */
	@SuppressWarnings("resource")
	//@Test
	public byte[] exportExcel() throws Exception {  
		HSSFWorkbook workbook = new HSSFWorkbook();
	    HSSFSheet sheet = workbook.createSheet("员工基本信息表");
	    //单元格标题格式(开始)
	    HSSFCellStyle cellStyle1 = workbook.createCellStyle();  //单元格标题
	    cellStyle1.setAlignment(HorizontalAlignment.CENTER);  
	    cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
	    cellStyle1.setWrapText(true);//自动换行
	    cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
	    cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
	    cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
	    cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
	    HSSFFont fonttitle = workbook.createFont();  
	    fonttitle.setColor(HSSFColor.BLACK.index);  
	    fonttitle.setBold(true);
	    fonttitle.setFontHeightInPoints((short) 16);;
	    cellStyle1.setFont(fonttitle);
	    //单元格标题格式（结束）
	  //单元格正文格式(开始)
	    HSSFCellStyle cellStyle = workbook.createCellStyle();  //单元格标题
	    cellStyle.setAlignment(HorizontalAlignment.CENTER);  
	    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	    cellStyle.setWrapText(true);//自动换行
	     HSSFFont font = workbook.createFont();  
	    font.setColor(HSSFColor.BLACK.index);  
	    font.setBold(true);
	    //font.setFontHeight((short)3);
	    cellStyle.setFont(font);
	    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
	  //单元格正文格式（结束）
        
        //单元格正文内容格式(开始)
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();  //单元格标题
        cellStyle2.setAlignment(HorizontalAlignment.CENTER);  
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle2.setWrapText(true);//自动换行
        HSSFFont font2 = workbook.createFont();  
        font2.setColor(HSSFColor.BLACK.index);  
        cellStyle2.setFont(font2);
        cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
        cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        //单元格正文格式（结束）
	    // 将第一行的8个单元格给合并
	   // sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
	    HSSFRow row = sheet.createRow(0);
	    //row.setHeight((short)18);
	    HSSFCell beginCell = row.createCell(0);
	    beginCell.setCellValue("员工基本信息表");  
	    row.createCell(1).setCellValue("");
	    row.createCell(2).setCellValue("");
        row.createCell(3).setCellValue("");
        row.createCell(4).setCellValue("");    
        row.createCell(5).setCellValue("");    
        row.createCell(6).setCellValue("");
        row.createCell(7).setCellValue("");
     // 将第一行的8个单元格给合并
	   // beginCell.setCellStyle(cellStyle1);
        setBorderStyle(row,cellStyle1);
	    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
	    setRegionStyle(sheet,new CellRangeAddress(0, 0, 0, 7),cellStyle1);
	   // row = sheet.createRow(1);
	    String[] strList= {"人员基本信息","姓名","性别","出生日期","民族","身份证号码","籍贯","照片"};
	    getTitle(strList,sheet,1,cellStyle);
	    strList=null;
	    row = sheet.createRow(2);    
	    row.createCell(0).setCellValue(""); 
	        if(staffAndOutStaff !=null) {
	         row.createCell(1).setCellValue(isNullString(staffAndOutStaff.getStaffName())); 
	         row.createCell(2).setCellValue(isNullString(staffAndOutStaff.getStaffSex()));  
		     row.createCell(3).setCellValue(isNullString(staffAndOutStaff.getStaffBirth()));    
		     row.createCell(4).setCellValue(isNullString(staffAndOutStaff.getStaffNation()));  
		     row.createCell(5).setCellValue(isNullString(staffAndOutStaff.getStaffNum()));  
		     row.createCell(6).setCellValue(isNullString(staffAndOutStaff.getNativeplace()));  
	        }else {
	        	 row.createCell(1).setCellValue(""); 
		         row.createCell(2).setCellValue("");  
			     row.createCell(3).setCellValue("");    
			     row.createCell(4).setCellValue(""); 
			     row.createCell(5).setCellValue("");  
			     row.createCell(6).setCellValue("");    
	        }
	        row.createCell(7).setCellValue("");
	        setBorderStyle(row,cellStyle2);
	      String[]  strList1= {"","政治面貌","入党团日期","参加工作日期","*婚姻状况","出生地","*户口所在地",""};
		   getTitle(strList1,sheet,3,cellStyle);
	    row = sheet.createRow(4);  
	    row.createCell(0).setCellValue(""); 
	  if(staffAndOutStaff !=null) {
	    	    row.createCell(1).setCellValue(isNullString(staffAndOutStaff.getStaffPolity()));  
		        row.createCell(2).setCellValue(isNullString(staffAndOutStaff.getPartydate()));  
		        row.createCell(3).setCellValue(isNullString(staffAndOutStaff.getStaffJoinWorkDate()));  
		        row.createCell(4).setCellValue(isNullString(staffAndOutStaff.getStaffMarital())); 
		        row.createCell(5).setCellValue(isNullString(staffAndOutStaff.getBirthplace()));  
		        row.createCell(6).setCellValue("");  
	        }else {
	        	 row.createCell(1).setCellValue(""); 
	        	 row.createCell(2).setCellValue("");  
		         row.createCell(3).setCellValue("");  
		         row.createCell(4).setCellValue("");  
			     row.createCell(5).setCellValue("");    
			     row.createCell(6).setCellValue("");  
	        }
	        row.createCell(7).setCellValue("");   
	        setBorderStyle(row,cellStyle2);
	        String[]  strList2= {"","办公电话","手机","电子邮箱","","紧急联系人","紧急联系人电话",""};
			   getTitle(strList2,sheet,5,cellStyle);
	        sheet.addMergedRegion(new CellRangeAddress(5, 5, 3, 4));
	    row = sheet.createRow(6);   
		    row.createCell(0).setCellValue("");  
		    if(staffAndOutStaff !=null) {
			    row.createCell(1).setCellValue(isNullString(staffAndOutStaff.getOfficephone()));
			    row.createCell(2).setCellValue(isNullString(staffAndOutStaff.getStaffMobile()));
			    row.createCell(3).setCellValue(isNullString(staffAndOutStaff.getStaffEmail()));
		        row.createCell(4).setCellValue("");    
		        row.createCell(5).setCellValue(isNullString(staffAndOutStaff.getUrgentman()));    
		        row.createCell(6).setCellValue(isNullString(staffAndOutStaff.getUrgentmanphone())); 
		    }else {
		    	row.createCell(1).setCellValue(""); 
	        	 row.createCell(2).setCellValue("");  
		         row.createCell(3).setCellValue("");  
		         row.createCell(4).setCellValue("");  
			     row.createCell(5).setCellValue("");    
			     row.createCell(6).setCellValue("");  
		    }
	        row.createCell(7).setCellValue("");  
	        setBorderStyle(row,cellStyle2);
	        sheet.addMergedRegion(new CellRangeAddress(6, 6, 3, 4)); //合并邮箱单元格     
	        sheet.addMergedRegion(new CellRangeAddress(1, 6, 0, 0));  //合并人员基本信息单元格 
	        setRegionStyle(sheet,new CellRangeAddress(1, 6, 0, 0),cellStyle);
	        sheet.addMergedRegion(new CellRangeAddress(1, 6, 7, 7));   //合并头像单元格
	        setRegionStyle(sheet,new CellRangeAddress(1, 6, 7, 7),cellStyle);
	       //工作信息
	        String[]  strList3= {"工作信息","部门","岗位序列","岗位","岗位等级","职务","现职时间","任现岗位等级时间"};
			   getTitle(strList3,sheet,7,cellStyle);
	        row = sheet.createRow(8);   
	        row.createCell(0).setCellValue("");  
	        if(staffAndOutStaff !=null) {
	        	row.createCell(1).setCellValue(isNullString(staffAndOutStaff.getDeptName()));
		        row.createCell(2).setCellValue(isNullString(staffAndOutStaff.getPostseries()));
		        row.createCell(3).setCellValue(isNullString(staffAndOutStaff.getStaffPostName()));
		        row.createCell(4).setCellValue(isNullString(staffAndOutStaff.getPostGrade()));    
		        row.createCell(5).setCellValue(isNullString(staffAndOutStaff.getJobGlbdef4()));    
		        row.createCell(6).setCellValue(isNullString(staffAndOutStaff.getJobGlbdef14()));
		        row.createCell(7).setCellValue(isNullString(staffAndOutStaff.getJobGlbdef8()));
	        }else {
	        	row.createCell(1).setCellValue("");
		        row.createCell(2).setCellValue("");
		        row.createCell(3).setCellValue("");
		        row.createCell(4).setCellValue("");    
		        row.createCell(5).setCellValue("");    
		        row.createCell(6).setCellValue("");
		        row.createCell(7).setCellValue("");
	        }
	        sheet.addMergedRegion(new CellRangeAddress(7, 8, 0,0));   
	        setBorderStyle(row,cellStyle2);
	        setRegionStyle(sheet,new CellRangeAddress(8, 8, 0, 7),cellStyle2);
	        int countrow=9;
	        //遍历的时候从第9行开始
	        String[]  strList4= {"学历信息","入学日期","毕业日期","学校名称","专业","学历","学位","学习方式"};
			   getTitle(strList4,sheet,countrow,cellStyle);
	        countrow=countrow+1;
	      int tempA=0;
	      
	      UecDocEdu uecDocEdu=new UecDocEdu();
	      if(findUecDocEduByPkPsndoc != null && findUecDocEduByPkPsndoc.size() > 0) {
	    	  //默认最少三行，不够的话为空
	    	  tempA=findUecDocEduByPkPsndoc.size();
	    	  if(tempA >=3) {
			        for(int i=0;i<findUecDocEduByPkPsndoc.size();i++) {
			        	uecDocEdu=findUecDocEduByPkPsndoc.get(i);
			        	row=sheet.createRow(i+countrow);
			        	row.createCell(0).setCellValue("");    
				        row.createCell(1).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getBegindate())?uecDocEdu.getBegindate():"");    
				        row.createCell(2).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getEnddate())?uecDocEdu.getEnddate():"");    
				        row.createCell(3).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getSchool())?uecDocEdu.getSchool():"");    
				        row.createCell(4).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getMajor())?uecDocEdu.getMajor():""); 
				        row.createCell(5).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getEducationName())?uecDocEdu.getEducationName():"");    
				        row.createCell(6).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getPkDegreeName())?uecDocEdu.getPkDegreeName():"");    
				        setBorderStyle(row,cellStyle2);
				        setRegionStyle(sheet,new CellRangeAddress(i+countrow, i+countrow, 0, 7),cellStyle2);
			        }
	             sheet.addMergedRegion(new CellRangeAddress(countrow-1, tempA+countrow-1, 0,0));
	             countrow= countrow+tempA;
	    	  }else {
	    		  for(int i=0;i<findUecDocEduByPkPsndoc.size();i++) {
			        	uecDocEdu=findUecDocEduByPkPsndoc.get(i);
			        	row=sheet.createRow(i+countrow);
			        	row.createCell(0).setCellValue("");    
			        	row.createCell(1).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getBegindate())?uecDocEdu.getBegindate():"");    
				        row.createCell(2).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getEnddate())?uecDocEdu.getEnddate():"");    
				        row.createCell(3).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getSchool())?uecDocEdu.getSchool():"");    
				        row.createCell(4).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getMajor())?uecDocEdu.getMajor():""); 
				        row.createCell(5).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getEducationName())?uecDocEdu.getEducationName():"");    
				        row.createCell(6).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getPkDegreeName())?uecDocEdu.getPkDegreeName():"");    
				        row.createCell(7).setCellValue(!StrUtil.isNullOrEmpty(uecDocEdu.getStudymodeName())?uecDocEdu.getStudymodeName():"");
				        setBorderStyle(row,cellStyle2);
				        setRegionStyle(sheet,new CellRangeAddress(i+countrow, i+countrow, 0, 7),cellStyle2);

	    		  }
	             for (int j = 0; j < 3-tempA; j++) {
	            	 row=sheet.createRow(j+countrow+tempA);
			        	row.createCell(0).setCellValue("");    
				        row.createCell(1).setCellValue("");    
				        row.createCell(2).setCellValue("");    
				        row.createCell(3).setCellValue("");    
				        row.createCell(4).setCellValue(""); 
				        row.createCell(5).setCellValue("");    
				        row.createCell(6).setCellValue("");    
				        row.createCell(7).setCellValue("");
				        setBorderStyle(row,cellStyle2);
				        setRegionStyle(sheet,new CellRangeAddress(j+countrow+tempA, j+countrow+tempA, 0, 7),cellStyle2);

				}
	             sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+2, 0,0));
	             countrow= countrow+3;
	    	  }
	      }else {
	    	  //默认三行为空
	    	  for(int i=0;i<3;i++) {
		        	row=sheet.createRow(i+countrow);
		        	row.createCell(0).setCellValue("");    
			        row.createCell(1).setCellValue("");    
			        row.createCell(2).setCellValue("");    
			        row.createCell(3).setCellValue("");    
			        row.createCell(4).setCellValue(""); 
			        row.createCell(5).setCellValue("");    
			        row.createCell(6).setCellValue("");    
			        row.createCell(7).setCellValue("");
			        setBorderStyle(row,cellStyle2);
			        setRegionStyle(sheet,new CellRangeAddress(i+countrow, i+countrow, 0, 7),cellStyle2);
		        }
	    	  sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+2, 0,0));
	    	  countrow= countrow+3;
	      }
	      //履历加载开始  
	      String[]  strList5= {"工作记录","开始时间","结束时间","工作单位","","工作部门","岗位","职务"};
		   getTitle(strList5,sheet,countrow,cellStyle);
	      countrow=countrow+1;
	    int tempb=0;
	    UecWork uecWork= new UecWork();
	    //默认加载5行
	    if(findUecWorkByPkPsndoc != null && findUecWorkByPkPsndoc.size()>0) {
	    	tempb=findUecWorkByPkPsndoc.size();
	    	if(tempb>=5) {
	    		for (int i = 0; i < tempb; i++) {
	    			uecWork=findUecWorkByPkPsndoc.get(i);
	    		  row=sheet.createRow(countrow+i);
		    	  row.createCell(0).setCellValue("");    
		    	  String a=isNullString(uecWork.getBegindate());
		    	  String b=isNullString(uecWork.getEnddate());
		    	  String c=isNullString(uecWork.getWorkcorp());
		    	  String d=isNullString(uecWork.getWorkdept());
		    	  String e=isNullString(uecWork.getWorkpost());
		    	  String f=isNullString(uecWork.getWorkjob());
		  	      row.createCell(1).setCellValue(a);    
		  	      row.createCell(2).setCellValue(b);    
		  	      row.createCell(3).setCellValue(c);    
		  	      row.createCell(4).setCellValue("");  
		  	      row.createCell(5).setCellValue(d);    
		  	      row.createCell(6).setCellValue(e);  
		  	      row.createCell(7).setCellValue(f); 
		  	    setBorderStyle(row,cellStyle2);
		  	      sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 3,4));
			        setRegionStyle(sheet,new CellRangeAddress(i+countrow, i+countrow, 0, 7),cellStyle2);

				}
	    		 sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+tempb-1, 0,0));
	    		 countrow=countrow+tempb;
	    	}
	    	else {
	    		for (int i = 0; i < tempb; i++) {
	    			uecWork=findUecWorkByPkPsndoc.get(i);
	    		  row=sheet.createRow(countrow+i);
	    		  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue(isNullString(uecWork.getBegindate()));    
		  	      row.createCell(2).setCellValue(isNullString(uecWork.getEnddate()));    
		  	      row.createCell(3).setCellValue(isNullString(uecWork.getWorkcorp()));    
		  	      row.createCell(4).setCellValue("");  
		  	      row.createCell(5).setCellValue(isNullString(uecWork.getWorkdept()));    
		  	      row.createCell(6).setCellValue(isNullString(uecWork.getWorkpost()));  
		  	      row.createCell(7).setCellValue(isNullString(uecWork.getWorkjob())); 
		  	    setBorderStyle(row,cellStyle2);
		  	      sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 3,4));
			      setRegionStyle(sheet,new CellRangeAddress(i+countrow, i+countrow, 0, 7),cellStyle2);
			      

				}
	    		for (int j = 0; j < 5-tempb; j++) {
	    		 row= sheet.createRow(countrow+tempb+j);
		    	  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue("");    
		  	      row.createCell(2).setCellValue("");    
		  	      row.createCell(3).setCellValue("");    
		  	      row.createCell(4).setCellValue("");  
		  	      row.createCell(5).setCellValue("");    
		  	      row.createCell(6).setCellValue("");  
		  	      row.createCell(7).setCellValue(""); 
		  	    setBorderStyle(row,cellStyle2);
		  	    setRegionStyle(sheet,new CellRangeAddress(countrow+tempb+j,countrow+tempb+j, 0, 7),cellStyle2);
		  	      sheet.addMergedRegion(new CellRangeAddress(countrow+tempb+j, countrow+tempb+j, 3,4));
				}
	    		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+4, 0,0));
	    		countrow=countrow+5;
	    	}
	    }
	   else {
	    	for (int j = 0; j < 5; j++) {
	    		  sheet.createRow(countrow+j);
		    	  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue("");    
		  	      row.createCell(2).setCellValue("");    
		  	      row.createCell(3).setCellValue("");    
		  	      row.createCell(4).setCellValue("");  
		  	      row.createCell(5).setCellValue("");    
		  	      row.createCell(6).setCellValue("");  
		  	      row.createCell(7).setCellValue(""); 
		  	    setBorderStyle(row,cellStyle2);
		  	      sheet.addMergedRegion(new CellRangeAddress(countrow+j, countrow+j, 3,4));
		  	    setRegionStyle(sheet,new CellRangeAddress(countrow+j,countrow+j, 0, 7),cellStyle2);
				}
	    		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+4, 0,0));
	    		countrow=countrow+5;
	    }
	    setBorderStyle(row,cellStyle2);
	    //考核信息加载
	      String[]  strList6= {"考核信息","起始日期","终止日期","考核等级","考核单位","","",""};
		   getTitle(strList6,sheet,countrow,cellStyle); 
	      sheet.addMergedRegion(new CellRangeAddress(countrow, countrow, 4,7));
	      countrow=countrow+1;
	    int tempD=0;
	    UecAss uecass = new UecAss();
	    if(findUecAssByPkPsndoc != null && findUecAssByPkPsndoc.size()>0) {
	    	tempD=findUecAssByPkPsndoc.size();
	    	if(tempD>=3) {
	    		for (int i = 0; i < tempD; i++) {
	    			uecass=findUecAssByPkPsndoc.get(i);
	    			 row = sheet.createRow(countrow+i);
	    			 row.createCell(0).setCellValue("");    
    			     row.createCell(1).setCellValue(isNullString(uecass.getBegindate()));    
    			     row.createCell(2).setCellValue(isNullString(uecass.getEnddate()));    
    			     row.createCell(3).setCellValue(isNullString(uecass.getPkPerankName()));    
    			     row.createCell(4).setCellValue(isNullString(uecass.getPkOrgassName()));    
    			     row.createCell(5).setCellValue("");    
    			     row.createCell(6).setCellValue("");    
    			     row.createCell(7).setCellValue(""); 
    			     setBorderStyle(row,cellStyle2);
    			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+i,countrow+i, 4, 7),cellStyle2);

    			    sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 4,7));
				}
	    		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+tempD-1, 0,0));
	    		 countrow=countrow+tempD;
	    	}else {
	    		for (int i = 0; i < tempD; i++) {
	    			uecass=findUecAssByPkPsndoc.get(i);
	    			 row = sheet.createRow(countrow+i);
	    			 row.createCell(0).setCellValue("");    
    			     row.createCell(1).setCellValue(isNullString(uecass.getBegindate()));    
    			     row.createCell(2).setCellValue(isNullString(uecass.getEnddate()));    
    			     row.createCell(3).setCellValue(isNullString(uecass.getPkPerankName()));    
    			     row.createCell(4).setCellValue(isNullString(uecass.getPkOrgassName()));    
    			     row.createCell(5).setCellValue("");    
    			     row.createCell(6).setCellValue("");    
    			     row.createCell(7).setCellValue(""); 
    			     setBorderStyle(row,cellStyle2);
    			    sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 4,7));
  			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+i,countrow+i, 4, 7),cellStyle2);

				}
	    		for (int j = 0; j < 3-tempD; j++) {
	    			 row = sheet.createRow(countrow+tempD+j);
	    			 row.createCell(0).setCellValue("");    
    			     row.createCell(1).setCellValue("");    
    			     row.createCell(2).setCellValue("");    
    			     row.createCell(3).setCellValue("");    
    			     row.createCell(4).setCellValue("");    
    			     row.createCell(5).setCellValue("");    
    			     row.createCell(6).setCellValue("");    
    			     row.createCell(7).setCellValue(""); 
    			     setBorderStyle(row,cellStyle2);
    			    sheet.addMergedRegion(new CellRangeAddress(countrow+tempD+j, countrow+tempD+j, 4,7));
  			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+tempD+j,countrow+tempD+j, 4, 7),cellStyle2);

	    		}
	    		sheet.addMergedRegion(new CellRangeAddress(countrow, countrow+2, 0,0));
	    		 countrow=countrow+3;
	    	}
	    }
	    else {
	    	for (int j = 0; j < 3; j++) {
   			 row = sheet.createRow(countrow+j);
   			 row.createCell(0).setCellValue("");    
			     row.createCell(1).setCellValue("");    
			     row.createCell(2).setCellValue("");    
			     row.createCell(3).setCellValue("");    
			     row.createCell(4).setCellValue("");    
			     row.createCell(5).setCellValue("");    
			     row.createCell(6).setCellValue("");    
			     row.createCell(7).setCellValue(""); 
			     setBorderStyle(row,cellStyle2);
			    sheet.addMergedRegion(new CellRangeAddress(countrow+j, countrow+j, 4,7));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+j,countrow+j, 4, 7),cellStyle2);

			}
   		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+2, 0,0));
   		countrow = countrow+3;
	    }
	    setBorderStyle(row,cellStyle2);
	    //家庭信息
	      String[]  strList11= {"家庭信息","与本人关系","姓名","出生日期","政治面貌","工作单位","","职位"};
		   getTitle(strList11,sheet,countrow,cellStyle); 
	      sheet.addMergedRegion(new CellRangeAddress(countrow, countrow, 5,6));
	      countrow=countrow+1;
	    int tempG=0;
	    UecFamily uecFamily= new UecFamily();
	    //默认加载2行
	    if(uecFamilyListByPkpsndoc != null && uecFamilyListByPkpsndoc.size()>0) {
	    	tempG=uecFamilyListByPkpsndoc.size();
	    	if(tempG>=2) {
	    		for (int i = 0; i < tempG; i++) {
	    			uecFamily=uecFamilyListByPkpsndoc.get(i);
	    			row=sheet.createRow(countrow+i);
		    	  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue(isNullString(uecFamily.getMemRelation()));    
		  	      row.createCell(2).setCellValue(isNullString(uecFamily.getMemName()));    
		  	      row.createCell(3).setCellValue(isNullString(uecFamily.getMemBirthday()));    
		  	      row.createCell(4).setCellValue(isNullString(uecFamily.getPoliticsName()));  
		  	      row.createCell(5).setCellValue(isNullString(uecFamily.getMemCorp()));    
		  	      row.createCell(6).setCellValue("");  
		  	    setBorderStyle(row,cellStyle2);
		  	      row.createCell(7).setCellValue(isNullString(uecFamily.getMemJob())); 
		  	    sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 5,6));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+i,countrow+i, 4, 7),cellStyle2);

				}
	    		 sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+tempG-1, 0,0));
	    		 countrow=countrow+tempG;
	    	}else {
	    		for (int i = 0; i < tempG; i++) {
	    			uecFamily=uecFamilyListByPkpsndoc.get(i);
	    			row=sheet.createRow(countrow+i);
	    			row.createCell(1).setCellValue(isNullString(uecFamily.getMemRelation()));    
			  	      row.createCell(2).setCellValue(isNullString(uecFamily.getMemName()));    
			  	      row.createCell(3).setCellValue(isNullString(uecFamily.getMemBirthday()));    
			  	      row.createCell(4).setCellValue(isNullString(uecFamily.getPoliticsName()));  
			  	      row.createCell(5).setCellValue(isNullString(uecFamily.getMemCorp()));    
			  	      row.createCell(6).setCellValue("");  
			  	    setBorderStyle(row,cellStyle2);
			  	      row.createCell(7).setCellValue(isNullString(uecFamily.getMemJob())); 
		  	    sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 5,6));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+i,countrow+i, 4, 7),cellStyle2);

				}
	    		for (int j = 0; j < 2-tempG; j++) {
	    			row= sheet.createRow(countrow+tempG+j);
		    	  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue("");    
		  	      row.createCell(2).setCellValue("");    
		  	      row.createCell(3).setCellValue("");    
		  	      row.createCell(4).setCellValue("");  
		  	      row.createCell(5).setCellValue("");    
		  	      row.createCell(6).setCellValue("");  
		  	      row.createCell(7).setCellValue(""); 
		  	    setBorderStyle(row,cellStyle2);
		  	      sheet.addMergedRegion(new CellRangeAddress(countrow+tempG+j, countrow+tempG+j, 5,6));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+tempG+j,countrow+tempG+j, 4, 7),cellStyle2);

	    		}
	    		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+1, 0,0));
	    		countrow=countrow+2;
	    	}
	    }else {
	    	for (int j = 0; j < 2; j++) {
	    		row=  sheet.createRow(countrow+j);
		    	  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue("");    
		  	      row.createCell(2).setCellValue("");    
		  	      row.createCell(3).setCellValue("");    
		  	      row.createCell(4).setCellValue("");  
		  	      row.createCell(5).setCellValue("");    
		  	      row.createCell(6).setCellValue("");  
		  	      row.createCell(7).setCellValue(""); 
		  	    setBorderStyle(row,cellStyle2);
		  	      sheet.addMergedRegion(new CellRangeAddress(countrow+j, countrow+j, 5,6));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+j,countrow+j, 4, 7),cellStyle2);

				}
	    		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+1, 0,0));
	    		countrow=countrow+2;
	    }
	    setBorderStyle(row,cellStyle2);
	    //专业技术职务加载
	    String[]  strList7= {"专业技术职务","技术职务名称","","","取得时间","","",""};
		   getTitle(strList7,sheet,countrow,cellStyle); 
	      sheet.addMergedRegion(new CellRangeAddress(countrow, countrow, 1,3));
	      sheet.addMergedRegion(new CellRangeAddress(countrow, countrow, 4,7));
	      countrow=countrow+1;
	    int tempE=0;
	    UecTitle uecTitle= new UecTitle();
	    //默认加载2行
	    if(uecTitleListByPkpsndoc != null && uecTitleListByPkpsndoc.size()>0) {
	    	tempE=uecTitleListByPkpsndoc.size();
	    	if(tempE>=2) {
	    		for (int i = 0; i < tempE; i++) {
	    			uecTitle=uecTitleListByPkpsndoc.get(i);
	    			row=sheet.createRow(countrow+i);
		    	  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue(isNullString(uecTitle.getPkTechposttitleName()));    
		  	      row.createCell(2).setCellValue("");    
		  	      row.createCell(3).setCellValue("");    
		  	      row.createCell(4).setCellValue(isNullString(uecTitle.getGlbdef4()));  
		  	      row.createCell(5).setCellValue("");    
		  	      row.createCell(6).setCellValue("");  
		  	      row.createCell(7).setCellValue(""); 
		  	    setBorderStyle(row,cellStyle2);
		  	    sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 1,3));
			      sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 4,7));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+i,countrow+i, 4, 7),cellStyle2);

				}
	    		 sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+tempE-1, 0,0));
	    		 countrow=countrow+tempE;
	    	}else {
	    		for (int i = 0; i < tempE; i++) {
	    			uecTitle=uecTitleListByPkpsndoc.get(i);
	    			row= sheet.createRow(countrow+i);
	    		  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue(isNullString(uecTitle.getPkTechposttitleName()));    
		  	      row.createCell(2).setCellValue("");    
		  	      row.createCell(3).setCellValue("");    
		  	      row.createCell(4).setCellValue(isNullString(uecTitle.getGlbdef4()));  
		  	      row.createCell(5).setCellValue("");    
		  	      row.createCell(6).setCellValue("");  
		  	      row.createCell(7).setCellValue(""); 
		  	    setBorderStyle(row,cellStyle2);
		  	    sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 1,3));
			      sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 4,7));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+i,countrow+i, 4, 7),cellStyle2);

				}
	    		for (int j = 0; j < 2-tempE; j++) {
	    			row=sheet.createRow(countrow+tempE+j);
		    	  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue("");    
		  	      row.createCell(2).setCellValue("");    
		  	      row.createCell(3).setCellValue("");    
		  	      row.createCell(4).setCellValue("");  
		  	      row.createCell(5).setCellValue("");    
		  	      row.createCell(6).setCellValue("");  
		  	      row.createCell(7).setCellValue(""); 
		  	    setBorderStyle(row,cellStyle2);
		  	      sheet.addMergedRegion(new CellRangeAddress(countrow+tempE+j, countrow+tempE+j, 1,3));
		  	      sheet.addMergedRegion(new CellRangeAddress(countrow+tempE+j, countrow+tempE+j, 4,7));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+j+tempE,countrow+j+tempE, 4, 7),cellStyle2);

				}
	    		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+1, 0,0));
	    		countrow=countrow+2;
	    	}
	    }else {
	    	for (int j = 0; j < 2; j++) {
	    		  row=sheet.createRow(countrow+j);
		    	  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue("");    
		  	      row.createCell(2).setCellValue("");    
		  	      row.createCell(3).setCellValue("");    
		  	      row.createCell(4).setCellValue("");  
		  	      row.createCell(5).setCellValue("");    
		  	      row.createCell(6).setCellValue("");  
		  	      row.createCell(7).setCellValue(""); 
		  	    setBorderStyle(row,cellStyle2);
		  	    sheet.addMergedRegion(new CellRangeAddress(countrow+j, countrow+j, 1,3));
		  	      sheet.addMergedRegion(new CellRangeAddress(countrow+j, countrow+j, 4,7));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+j,countrow+j, 4, 7),cellStyle2);

				}
	    		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+1, 0,0));
	    		countrow=countrow+2;
	    }
	    setBorderStyle(row,cellStyle2);
	    //职业资格加载
	      String[]  strList8= {"职业资格","职业资格名称","取得日期","资格等级","","批准单位","",""};
		   getTitle(strList8,sheet,countrow,cellStyle); 
	      sheet.addMergedRegion(new CellRangeAddress(countrow, countrow, 3,4));
	      sheet.addMergedRegion(new CellRangeAddress(countrow, countrow, 5,7));
	      countrow=countrow+1;
	    int tempF=0;
	    UecNationduty uecNationduty= new UecNationduty();
	    //默认加载2行
	    if(uecNationdutyListByPkpsndoc != null && uecNationdutyListByPkpsndoc.size()>0) {
	    	tempF=uecNationdutyListByPkpsndoc.size();
	    	if(tempF>=2) {
	    		for (int i = 0; i < tempF; i++) {
	    			uecNationduty=uecNationdutyListByPkpsndoc.get(i);
	    			row=sheet.createRow(countrow+i);
		    	  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue(isNullString(uecNationduty.getWorknameName()));    
		  	      row.createCell(2).setCellValue(isNullString(uecNationduty.getWorkdate()));    
		  	      row.createCell(3).setCellValue(isNullString(uecNationduty.getWorklevelName()));    
		  	      row.createCell(4).setCellValue("");  
		  	      row.createCell(5).setCellValue(isNullString(uecNationduty.getWorkunitname()));    
		  	      row.createCell(6).setCellValue("");  
		  	      row.createCell(7).setCellValue(""); 
		  	    setBorderStyle(row,cellStyle2);
		  	    sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 3,4));
			      sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 5,7));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+i,countrow+i, 4, 7),cellStyle2);

				}
	    		 sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+tempF-1, 0,0));
	    		 countrow=countrow+tempF;
	    	}else {
	    		for (int i = 0; i < tempF; i++) {
	    			uecNationduty=uecNationdutyListByPkpsndoc.get(i);
	    			row=sheet.createRow(countrow+i);
		    	  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue(isNullString(uecNationduty.getWorknameName()));    
		  	      row.createCell(2).setCellValue(isNullString(uecNationduty.getWorkdate()));    
		  	      row.createCell(3).setCellValue(isNullString(uecNationduty.getWorklevelName()));    
		  	      row.createCell(4).setCellValue("");  
		  	      row.createCell(5).setCellValue(isNullString(uecNationduty.getWorkunitname()));    
		  	      row.createCell(6).setCellValue("");  
		  	      row.createCell(7).setCellValue(""); 
		  	    setBorderStyle(row,cellStyle2);
		  	    sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 3,4));
			      sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 5,7));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+i,countrow+i, 4, 7),cellStyle2);

				}
	    		for (int j = 0; j < 2-tempF; j++) {
	    			row= sheet.createRow(countrow+tempF+j);
		    	  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue("");    
		  	      row.createCell(2).setCellValue("");    
		  	      row.createCell(3).setCellValue("");    
		  	      row.createCell(4).setCellValue("");  
		  	      row.createCell(5).setCellValue("");    
		  	      row.createCell(6).setCellValue("");  
		  	      row.createCell(7).setCellValue(""); 
		  	    setBorderStyle(row,cellStyle2);
		  	      sheet.addMergedRegion(new CellRangeAddress(countrow+tempF+j, countrow+tempF+j, 3,4));
		  	      sheet.addMergedRegion(new CellRangeAddress(countrow+tempF+j, countrow+tempF+j, 5,7));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+j+tempF,countrow+j+tempF, 4, 7),cellStyle2);

	    		}
	    		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+1, 0,0));
	    		countrow=countrow+2;
	    	}
	    }else {
	    	for (int j = 0; j < 2; j++) {
	    		row=  sheet.createRow(countrow+j);
		    	  row.createCell(0).setCellValue("");    
		  	      row.createCell(1).setCellValue("");    
		  	      row.createCell(2).setCellValue("");    
		  	      row.createCell(3).setCellValue("");    
		  	      row.createCell(4).setCellValue("");  
		  	      row.createCell(5).setCellValue("");    
		  	      row.createCell(6).setCellValue("");  
		  	      row.createCell(7).setCellValue(""); 
		  	    setBorderStyle(row,cellStyle2);
		  	    sheet.addMergedRegion(new CellRangeAddress(countrow+j, countrow+j, 3,4));
		  	      sheet.addMergedRegion(new CellRangeAddress(countrow+j, countrow+j, 5,7));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+j,countrow+j, 4, 7),cellStyle2);

				}
	    		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+1, 0,0));
	    		countrow=countrow+2;
	    }
	    setBorderStyle(row,cellStyle2);
	    //奖励加载开始
	    int tempC=0;
	    UecEnc uecEnc= new UecEnc();
	    String[]  strList9= {"奖励情况","奖励类型","奖励机构","奖励级别","奖励时间","奖励事由","","奖励名称"};
		   getTitle(strList9,sheet,countrow,cellStyle); 
	    sheet.addMergedRegion(new CellRangeAddress(countrow, countrow, 5,6));
	    countrow=countrow+1;
	    if(findUecEncByPkPsndoc != null && findUecEncByPkPsndoc.size() > 0) {
	    	tempC=findUecEncByPkPsndoc.size();
	    	if(tempC>=3) {
	    		for (int i = 0; i < tempC; i++) {
	    			uecEnc=findUecEncByPkPsndoc.get(i);
	    			 row = sheet.createRow(countrow+i);
	    			 row.createCell(0).setCellValue("");    
    			     row.createCell(1).setCellValue(isNullString(uecEnc.getEncourtype()));    
    			     row.createCell(2).setCellValue(isNullString(uecEnc.getEncourorg()));    
    			     row.createCell(3).setCellValue(isNullString(uecEnc.getEncourrankName()));    
    			     row.createCell(4).setCellValue(isNullString(uecEnc.getEncourdate()));    
    			     row.createCell(5).setCellValue(isNullString(uecEnc.getEncourmatter()));    
    			     row.createCell(6).setCellValue("");  
    			     setBorderStyle(row,cellStyle2);
    			     row.createCell(7).setCellValue(isNullString(uecEnc.getGlbdef1())); 
    			    sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 5,6));
  			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+i,countrow+i, 4, 7),cellStyle2);

				}
	    		sheet.addMergedRegion(new CellRangeAddress(countrow, countrow+tempC-1, 0,0));
	    		 countrow=countrow+tempC;
	    	}else {
	    		for (int i = 0; i < tempC; i++) {
	    			uecEnc=findUecEncByPkPsndoc.get(i);
	    			 row = sheet.createRow(countrow+i);
	    			 row.createCell(0).setCellValue("");    
   			     row.createCell(1).setCellValue(isNullString(uecEnc.getEncourtype()));    
   			     row.createCell(2).setCellValue(isNullString(uecEnc.getEncourorg()));    
   			     row.createCell(3).setCellValue(isNullString(uecEnc.getEncourrankName()));    
   			     row.createCell(4).setCellValue(isNullString(uecEnc.getEncourdate()));    
   			     row.createCell(5).setCellValue(isNullString(uecEnc.getEncourmatter()));    
   			     row.createCell(6).setCellValue("");    
   			     row.createCell(7).setCellValue(isNullString(uecEnc.getGlbdef1())); 
   			  setBorderStyle(row,cellStyle2);
    			    sheet.addMergedRegion(new CellRangeAddress(countrow+i, countrow+i, 5,6));
  			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+i,countrow+i, 4, 7),cellStyle2);

				}
	    		for (int j = 0; j < 3-tempC; j++) {
	    			 row = sheet.createRow(countrow+tempC+j);
	    			 row.createCell(0).setCellValue("");    
    			     row.createCell(1).setCellValue("");    
    			     row.createCell(2).setCellValue("");    
    			     row.createCell(3).setCellValue("");    
    			     row.createCell(4).setCellValue("");    
    			     row.createCell(5).setCellValue("");    
    			     row.createCell(6).setCellValue("");    
    			     row.createCell(7).setCellValue(""); 
    			     setBorderStyle(row,cellStyle2);
    			    sheet.addMergedRegion(new CellRangeAddress(countrow+tempC+j, countrow+tempC+j, 5,6));
  			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+j+tempC,countrow+j+tempC, 4, 7),cellStyle2);

	    		}
	    		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+2, 0,0));
	    		 countrow=countrow+3;
	    	}
	    }
	    else {
	    	for (int j = 0; j < 3; j++) {
   			 row = sheet.createRow(countrow+j);
   			 row.createCell(0).setCellValue("");   
			     row.createCell(1).setCellValue("");    
			     row.createCell(2).setCellValue("");    
			     row.createCell(3).setCellValue("");    
			     row.createCell(4).setCellValue("");    
			     row.createCell(5).setCellValue("");    
			     row.createCell(6).setCellValue("");    
			     row.createCell(7).setCellValue(""); 
			     setBorderStyle(row,cellStyle2);
			    sheet.addMergedRegion(new CellRangeAddress(countrow+j, countrow+j, 5,6));
			  	  setRegionStyle(sheet,new CellRangeAddress(countrow+j,countrow+j, 4, 7),cellStyle2);

			}
   		sheet.addMergedRegion(new CellRangeAddress(countrow-1, countrow+2, 0,0));
   		countrow=countrow+3;
	    }
	    setBorderStyle(row,cellStyle2);
	    String[]  strList10= {"备注","标识为*的项目，员工通过自助系统直接编辑修改。其他项目按照员工基本信息表的核对要求，提交纸质文件。","","","","","",""};
		   getTitle(strList10,sheet,countrow,cellStyle); 
	    sheet.addMergedRegion(new CellRangeAddress(countrow, countrow, 1,7));
	    //到这里，excel就已经生成了，然后就需要通过流来写出去
	    sheet.setDefaultColumnWidth(10);//设置列宽
	    sheet.setDefaultRowHeight((short)8);
	    /*sheet.setHorizontallyCenter(true);
	    sheet.setVerticallyCenter(true);
	    sheet.setPrintGridlines(true);
	    sheet.setMargin((short)2, 2);
	    sheet.setDisplayGridlines(true);*/
	    /**
		 * 通过浏览器下载
		 */
	   ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
	    workbook.write(byteArrayOutputStream);
	  //将流写进response输出流中
		if (byteArrayOutputStream != null) {
			byte[] bs = byteArrayOutputStream.toByteArray();
			byteArrayOutputStream.close();
			return bs;
		}
		return null;

		/* FileOutputStream fout = new FileOutputStream("C:/Users/BMP/Desktop/Test.xls");
	    workbook.write(fout);*/
	}
   
	 public String dataTraslate(String data) throws ParseException  {
	    	SimpleDateFormat df=new SimpleDateFormat("yyyy-mm-dd");
	    	Date parse = df.parse(data);
	    	String newDate = df.format(parse);
	    	return newDate;
	    }
	 //是否为空，不为空返回原值，否则返回" "
	 public String isNullString(String data){
		   String datanew =StrUtil.isNullOrEmpty(data)?"":data;
		 return datanew;
	 }
	/**
	 * 给标题单元格设置格式
	 * @param strList   单元格内容，字符串数组
	 * @param sheet     
	 * @param rowIndex   定义行索引，从0开始，需要手工输入
	 * @param cellStyle  单元格样式
	 * @return
	 */
	public HSSFCell getTitle(String[] strList,HSSFSheet sheet,int rowIndex,HSSFCellStyle cellStyle) {
		if(strList != null && strList.length>0) {
			beginCell=null;
			 row = sheet.createRow(rowIndex);
			for (int i = 0; i < strList.length; i++) {
		    	beginCell = row.createCell(i);
		    	beginCell.setCellValue(strList[i]);
		    	beginCell.setCellStyle(cellStyle);
			}
		}
		return beginCell;
	}
	
	/**
	 * 给标题单元格设置格式
	 * @param strList   单元格内容，字符串数组
	 * @param sheet     
	 * @param rowIndex   定义行索引，从0开始，需要手工输入
	 * @param cellStyle  单元格样式
	 * @return
	 */
	public void setBorderStyle(HSSFRow row,HSSFCellStyle cellStyle) {
	      row.getCell(0).setCellStyle(cellStyle);
			for (int i = row.getFirstCellNum(); i < row.getLastCellNum()-1; i++) {
				 row.getCell(i).setCellStyle(cellStyle);
				 
		}
		//return row;
	}
	public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress region,
            HSSFCellStyle cs) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            HSSFRow row = HSSFCellUtil.getRow(i, sheet);
            HSSFCell cell = null;
            //循环设置单元格样式
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                cell = HSSFCellUtil.getCell(row, (short) j);
                cell.setCellStyle(cs);
            }
         //   System.out.println("r2==========="+i);
        }
        
    }
}